﻿Imports System.Data.SqlClient
Public Class FornecedorDAL

#Region "Incluir/Alterar"
    Public Function InserirAlterar(codigo As Integer, razao As String, fantasia As String, endereco As String, num As String, bairro As String, cidade As String, uf As String,
                                   complemento As String, cep As String, email As String, obs As String, status As Integer,
                                   tpe_codigo As Integer, cnpj As String, inscricao As String,
                                   telres As String, telcom As String, telcel As String, telfax As String, usuario As Integer) As Integer
        Dim cmd = New SqlCommand
        Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "SPFornecedor"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@razao", SqlDbType.VarChar)).Value = razao
            cmd.Parameters.Add(New SqlParameter("@fantasia", SqlDbType.VarChar)).Value = fantasia
            cmd.Parameters.Add(New SqlParameter("@endereco", SqlDbType.VarChar)).Value = endereco
            cmd.Parameters.Add(New SqlParameter("@bairro", SqlDbType.VarChar)).Value = bairro
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.VarChar)).Value = cidade
            cmd.Parameters.Add(New SqlParameter("@numero", SqlDbType.VarChar)).Value = num
            cmd.Parameters.Add(New SqlParameter("@telres", SqlDbType.VarChar)).Value = telres
            cmd.Parameters.Add(New SqlParameter("@telcom", SqlDbType.VarChar)).Value = telcom
            cmd.Parameters.Add(New SqlParameter("@telcel", SqlDbType.VarChar)).Value = telcel
            cmd.Parameters.Add(New SqlParameter("@telfax", SqlDbType.VarChar)).Value = telfax
            cmd.Parameters.Add(New SqlParameter("@cep", SqlDbType.VarChar)).Value = cep
            cmd.Parameters.Add(New SqlParameter("@uf", SqlDbType.VarChar)).Value = uf
            cmd.Parameters.Add(New SqlParameter("@codclas", SqlDbType.Int)).Value = 1
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = status
            cmd.Parameters.Add(New SqlParameter("@obs", SqlDbType.VarChar)).Value = obs
            cmd.Parameters.Add(New SqlParameter("@codtp", SqlDbType.Int)).Value = 2
            cmd.Parameters.Add(New SqlParameter("@cnpj", SqlDbType.VarChar)).Value = cnpj
            cmd.Parameters.Add(New SqlParameter("@ie", SqlDbType.VarChar)).Value = inscricao
            cmd.Parameters.Add(New SqlParameter("@complemento", SqlDbType.VarChar)).Value = complemento
            cmd.Parameters.Add(New SqlParameter("@email", SqlDbType.VarChar)).Value = email
            cmd.Parameters.Add(New SqlParameter("@usu_codigo", SqlDbType.Int)).Value = usuario
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_EstFornecedor where for_codigo= @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_EstFornecedor where for_razao COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 2
                    cmd.CommandText = "select * from vw_EstFornecedor where for_fantasia COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 3
                    cmd.CommandText = "select * from vw_EstFornecedor where for_cnpj = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.BigInt)).Value = Val(Descricao)
                Case 4
                    cmd.CommandText = "select * from vw_EstFornecedor where for_inscricao= @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.BigInt)).Value = Val(Descricao)
                Case 5
                    cmd.CommandText = "select * from vw_EstFornecedor where sta_codigo = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.Int)).Value = Descricao
                Case Else
                    cmd.CommandText = "select * from vw_EstFornecedor where sta_codigo < 3 order by for_razao"
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

End Class
